#!/bin/bash
APP=online_edu

# 如果输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi

dws_traffic_user_video_finish_1d="
insert overwrite table ${APP}.dws_traffic_user_video_finish_1d partition (dt)
select user_id,
       t3.course_id,
       t3.chapter_id,
       t3.video_id,
       position_sec,
       position_sec / during_sec * 100                                    play_sec_rate,
       if((position_sec / during_sec) * 100 >= 90, date_id, '9999-12-31') finish_date,
       if((position_sec / during_sec) * 100 >= 90, date_id, '9999-12-31')
from (
         select if(t1.user_id is not null, t1.user_id, t2.user_id)          user_id,
                if(t1.course_id is not null, t1.course_id, t2.course_id)    course_id,
                if(t1.chapter_id is not null, t1.chapter_id, t2.chapter_id) chapter_id,
                if(t1.video_id is not null, t1.video_id, t2.video_id)       video_id,
                if(cast(if(t1.position_sec is not null, t1.position_sec, 0) as BIGINT) >
                   cast(if(t2.position_sec is not null, t2.position_sec, 0) as BIGINT),
                   cast(if(t1.position_sec is not null, t1.position_sec, 0) as BIGINT),
                   cast(if(t2.position_sec is not null, t2.position_sec, 0) as BIGINT)
                    )                                                       position_sec,
                if(t1.finish_data is not null, t1.finish_data, t2.date_id)  date_id
         from (
                  select user_id,
                         course_id,
                         chapter_id,
                         video_id,
                         position_sec,
                         play_sec_rate,
                         finish_data
                  from ${APP}.dws_traffic_user_video_finish_1d
                  where dt = '9999-12-31') t1
                  full join
              (
                  select user_id,
                         course_id,
                         chapter_id,
                         video_id,
                         max(position_sec) position_sec,
                         max(date_id)      date_id
                  from ${APP}.dwd_traffic_video_play_inc
                  where dt = '$do_date'
                  group by course_id, user_id, chapter_id, video_id
              ) t2
              on t1.video_id = t2.video_id and t1.user_id = t2.user_id
     ) t3
         left join ${APP}.dim_video_full t4
                   on t4.dt = '$do_date'
                       and t4.id = t3.video_id;

"
dws_traffic_session_page_view_1d="
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition(dt='$do_date')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    sum(during_time),
    count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by session_id,mid_id,brand,model,operate_system,version_code,channel;
"

case $1 in
    "dws_traffic_user_video_finish_1d" )
        hive -e "$dws_traffic_user_video_finish_1d"
    ;;

    "dws_traffic_session_page_view_1d" )
        hive -e "$dwd_traffic_page_view_inc"
    ;;

    "all" )
        hive -e "$dws_traffic_user_video_finish_1d$dwd_traffic_page_view_inc"
    
    ;;
esac
